package com.shuhe.metastore;

import com.shuhe.JettyServer;
import com.shuhe.domain.neo4j.NodeAttr;
import com.shuhe.domain.neo4j.NodeBase;
import com.shuhe.domain.neo4j.NodeRelation;
import org.apache.log4j.Logger;
import org.neo4j.driver.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import static com.shuhe.common.CommonFunction.closeDb;

public class Neo4jDB {

    private static Logger logger = Logger.getLogger(Neo4jDB.class);

    public static String url = JettyServer.globalProperties.getProperty("neo4j.url");
    private static String username = JettyServer.globalProperties.getProperty("neo4j.user");
    private static String password = JettyServer.globalProperties.getProperty("neo4j.password");


    public static void makeData(String tableName) {
        Driver driver = null;
        Session session = null;
        Connection metaConn = null;
        Connection dbConn = null;
        try {
            metaConn = MetaMysql.connect();
            dbConn = DBSql.connect();
            driver = GraphDatabase.driver(url, AuthTokens.basic(username, password));
            session = driver.session();
            //加载实体基本配置
            String sql = "select node_e_name, node_c_name, node_color, node_id_field, node_name_field, " +
                    "node_icon from graph_node_base";
            ResultSet rs = MetaMysql.querySql(metaConn, sql);
            ArrayList<NodeBase> bases = new ArrayList<>();
            while (rs.next()) {
                NodeBase base = new NodeBase();
                base.setNode_e_name(rs.getString("node_e_name"));
                base.setNode_c_name(rs.getString("node_c_name"));
                base.setNode_color(rs.getString("node_color"));
                base.setNode_id_field(rs.getString("node_id_field"));
                base.setNode_name_field(rs.getString("node_name_field"));
                base.setNode_icon(rs.getString("node_icon"));
                bases.add(base);
            }
            //加载实体与实体关系配置
            sql = "select table_name, source_node_type, target_node_type, source_node_id_field, target_node_id_field, " +
                    "source_node_name_field, target_node_name_field, relation_field, source_node_attribute, " +
                    "target_node_attribute " +
                    "from graph_node_relation where table_name = '" + tableName + "'";
            rs = MetaMysql.querySql(metaConn, sql);
            NodeRelation relation = new NodeRelation();
            while (rs.next()) {
                relation.setTable_name(rs.getString("table_name"));
                relation.setSource_node_type(rs.getString("source_node_type"));
                relation.setTarget_node_type(rs.getString("target_node_type"));
                relation.setSource_node_id_field(rs.getString("source_node_id_field"));
                relation.setTarget_node_id_field(rs.getString("target_node_id_field"));
                relation.setSource_node_name_field(rs.getString("source_node_name_field"));
                relation.setTarget_node_name_field(rs.getString("target_node_name_field"));
                relation.setRelation_field(rs.getString("relation_field"));
                relation.setSource_node_attribute(rs.getString("source_node_attribute"));
                relation.setTarget_node_attribute(rs.getString("target_node_attribute"));
            }

            sql = "select column_name, column_comment from mxgc_user_column " +
                    "where concat_ws('.', db_name, table_name) = '" + tableName + "'";
            rs = MetaMysql.querySql(metaConn, sql);
            Map<String, String> attr_chs = new HashMap();
            while (rs.next()) {
                attr_chs.put(rs.getString("column_name"), rs.getString("column_comment"));
            }


            //循环实体数据
            sql = "select * from " + relation.getTable_name();
            rs = MetaMysql.querySql(dbConn, sql);
            while (rs.next()) {
                String source_node_id_field = relation.getSource_node_id_field();
                String target_node_id_field = relation.getTarget_node_id_field();
                String source_node_id_field_value = rs.getString(source_node_id_field);
                String target_node_id_field_value = rs.getString(target_node_id_field);
                String source_node_name_field = relation.getSource_node_name_field();
                String target_node_name_field = relation.getTarget_node_name_field();
                String source_node_name_field_value = rs.getString(source_node_name_field);
                String target_node_name_field_value = rs.getString(target_node_name_field);
                if (source_node_id_field_value == null || target_node_id_field_value == null
                        || source_node_id_field_value.length() <= 0 || target_node_id_field_value.length() <= 0) {
                    //有异常数据，直接执行下一条
                    continue;
                }
                String source_node_type = relation.getSource_node_type();
                String target_node_type = relation.getTarget_node_type();
                String source_node_attribute = relation.getSource_node_attribute();
                String[] sourceAttrs = source_node_attribute.split(",");
                String target_node_attribute = relation.getTarget_node_attribute();
                String[] targetAttrs = target_node_attribute.split(",");
                String neo4j_source_node_id_field = "";
                String neo4j_target_node_id_field = "";
                String neo4j_source_node_name_field = "";
                String neo4j_target_node_name_field = "";
                for (int i = 0; i < bases.size(); i++) {
                    NodeBase base = bases.get(i);
                    if (base.getNode_e_name().equals(source_node_type)) {
                        neo4j_source_node_id_field = base.getNode_id_field();
                        neo4j_source_node_name_field = base.getNode_name_field();
                    }
                    if (base.getNode_e_name().equals(target_node_type)) {
                        neo4j_target_node_id_field = base.getNode_id_field();
                        neo4j_target_node_name_field = base.getNode_name_field();
                    }
                }
                String relationValue = rs.getString(relation.getRelation_field());

                ArrayList<NodeAttr> sourceAttrList = new ArrayList<>();
                ArrayList<NodeAttr> targetAttrList = new ArrayList<>();
                for (int i = 0; i < sourceAttrs.length; i++) {
                    String en = sourceAttrs[i];
                    String ch = attr_chs.get(en);
                    NodeAttr attr = new NodeAttr();
                    attr.setColumn_name(en);
                    attr.setColumn_comment(ch);
                    sourceAttrList.add(attr);
                }
                for (int i = 0; i < targetAttrs.length; i++) {
                    String en = targetAttrs[i];
                    String ch = sourceAttrList.get(i).getColumn_comment();
                    NodeAttr attr = new NodeAttr();
                    attr.setColumn_name(en);
                    attr.setColumn_comment(ch);
                    targetAttrList.add(attr);
                }

                //执行源实体的查询
                String sourQuery = "MATCH (n0:" + source_node_type + ") " +
                        "WHERE n0." + neo4j_source_node_id_field + " = '" + source_node_id_field_value + "' return n0";
                //实体更新与插入
                updateOrInsert(sourQuery, session, sourceAttrList, rs, source_node_type,
                        neo4j_source_node_id_field, source_node_id_field_value,
                        neo4j_source_node_name_field, source_node_name_field_value,
                        "n0");


                //执行目的实体的查询
                String targetQuery = "MATCH (n1:" + target_node_type + ") " +
                        "WHERE n1." + neo4j_target_node_id_field + " = '" + target_node_id_field_value + "' return n1";
                //实体更新与插入
                updateOrInsert(targetQuery, session, targetAttrList, rs, target_node_type,
                        neo4j_target_node_id_field, target_node_id_field_value,
                        neo4j_target_node_name_field, target_node_name_field_value,
                        "n1");

                //建立关联关系
                String relationSql = sourQuery.substring(0, sourQuery.length() - 9) + " " +
                        targetQuery.substring(0, targetQuery.length() - 9)
                        + "match (n0)-[r {relationShip: '" + relationValue + "'}]->(n1) return r";
                Result result = session.run(relationSql);
                if (!result.hasNext()) {
                    relationSql = sourQuery.substring(0, sourQuery.length() - 9) + " " +
                            targetQuery.substring(0, targetQuery.length() - 9)
                            + "create (n0)-[:" + relationValue + " {relationShip: '" + relationValue + "'}]->(n1)";
                    session.run(relationSql);
                }


            }


        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (session != null) {
                session.close();
            }
            if (driver != null) {
                driver.close();
            }
            closeDb(metaConn);
            closeDb(dbConn);
        }
    }


    private static void updateOrInsert(String queryStr, Session session,
                                       ArrayList<NodeAttr> attrs, ResultSet rs, String node_type,
                                       String neo4j_node_id_field, String node_id_field_value,
                                       String neo4j_node_name_field, String node_name_field_value,
                                       String alias
    ) throws Exception {
        Result result = session.run(queryStr);
        if (result.hasNext()) {
            //源实体再neo4j中存在，执行更新操作
            String tmpStr = queryStr.substring(0, queryStr.length() - 9) + " set ";
            for (int i = 0; i < attrs.size(); i++) {
                tmpStr = tmpStr + alias + "." + attrs.get(i).getColumn_comment() + " = '"
                        + rs.getString(attrs.get(i).getColumn_name()) + "', ";
            }
            tmpStr = tmpStr.substring(0, tmpStr.length() - 2);
            session.run(tmpStr);
        } else {
            insert(queryStr, session, attrs, rs, node_type, neo4j_node_id_field, node_id_field_value,
                    neo4j_node_name_field, node_name_field_value, alias);
        }
    }

    private static void insert(String queryStr, Session session,
                               ArrayList<NodeAttr> attrs, ResultSet rs, String node_type,
                               String neo4j_node_id_field, String node_id_field_value,
                               String neo4j_node_name_field, String node_name_field_value,
                               String alias) throws Exception {
        //源实体再neo4j中不存在，执行插入操作
        String tmpStr = "CREATE (" + alias + ":" + node_type
                + " {" + neo4j_node_id_field + ": '" + node_id_field_value + "', "
                + neo4j_node_name_field + ": '" + node_name_field_value + "' ";
        if (attrs.size() <= 0) {
            tmpStr = tmpStr + "})";
        } else {
            tmpStr = tmpStr + ", ";
            for (int i = 0; i < attrs.size(); i++) {
                tmpStr = tmpStr + attrs.get(i).getColumn_comment() + ": '"
                        + rs.getString(attrs.get(i).getColumn_name()) + "', ";
            }
            tmpStr = tmpStr.substring(0, tmpStr.length() - 2);
            tmpStr = tmpStr + "})";
        }
        session.run(tmpStr);
    }
}
